/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class TestJDBC {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        try {
            conn = Main.getOracleConnection();
            String sql = "select c.approveDate approveDate,i.invoiceNo invoiceNo"
                    + " from ContractCavPass c,LInvoiceHead i"
                    + " where c.contractNo=i.invoiceNo and i.invoiceType=2"
                    + " and to_char(c.approveDate,'yyyy-MM-dd')>='2011-08-15'"
                    + " and to_char(c.approveDate,'yyyy-MM-dd')<='2011-08-25'"
                    + " and substr(c.contractNo,8,1)='L' ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            pstmt2 = conn.prepareStatement("select count(*) from LInvoiceHead where invoiceNo=?");
//            pstmt2 = conn.prepareStatement("update LInvoiceHead set invoiceDate=? where invoiceNo=?");
            int count = 0;
            while (rs.next()) {
                Date approveDate = rs.getDate("approveDate");
                String invoiceNo = rs.getString("invoiceNo");
//                System.out.println("approveDate=" + df.format(approveDate) + "  invoiceNo=" + invoiceNo);

//                pstmt2.setDate(1,  approveDate);
                pstmt2.setString(1, invoiceNo);
                rs2 = pstmt2.executeQuery();
                while (rs2.next()) {
                    int c = rs2.getInt(1);
                    if (c == 2) {
                        System.out.println("invoiceNo = " + invoiceNo);
                        System.out.println("c = " + c);
                    }
                    count++;
                }
//                count +=i;
            }
            System.out.println("count = " + count);

        } catch (Exception ex) {
            ex.printStackTrace();
            Logger.getLogger(TestJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                rs2.close();
                pstmt.close();
                pstmt2.close();
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
                Logger.getLogger(TestJDBC.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
